<?php
include_once ("Login/databaseconnection.php");
$qry=$_POST['query'];
$table_name=$_POST['Exc_table_name'];

$exp=new ExportPHP();
//Note: Please use anyone of the below code.Or make proper conditions
//Export to XLS
//$exp->exportWithQuery($qry,$table_name,"report.xls","xls");
//Export to DOC
$exp->exportWithQuery($qry,$table_name,$table_name.".doc","doc");
//Export to CSV
//$exp->exportWithQuery($qry,$table_name,"report.csv","csv");

class ExportPHP
{
// method for Excel file
function setHeaderXLS($file_name)
{
header("Content-type: application/ms-excel");
header("Content-Disposition: attachment; filename=$file_name");
header("Pragma: no-cache");
header("Expires: 0");
}
// method for Doc file
function setHeaderDoc($file_name)
{
header("Content-type: application/x-ms-download");
header("Content-Disposition: attachment; filename=$file_name");
header('Cache-Control: public');
}
// method for CSV file
function setHeaderCSV($file_name)
{
header("Content-type: application/csv");
header("Content-Disposition: inline; filename=$file_name");
}

function exportWithQuery($qury,$table_name,$file_name,$type)
{
$where_exi=strstr($qury,"@");
if($where_exi=="")
{
if($table_name=="training_details")
{
$heading="All trained teachers' details...";
}
else if($table_name=="resource_team")
{
$heading="All resource members' details...";
}
else if($table_name=="resource_details")
{
$heading="All resources' details...";
}
else if($table_name=="programmes"||$table_name=="students")
{
$heading="All ".str_replace("_"," ",$table_name)."' details...";
}
else
{
$heading="All ".str_replace("_"," ",$table_name)."s' details...";
}
}
if($heading=="")
{ 
if($table_name=="training_details")
{
$heading="Fitered trained teachers' details...";
}
else if($table_name=="resource_team")
{
$heading="Fitered resource members' details...";
}
else if($table_name=="resource_details")
{
$heading="Fitered resources' details...";
}
else if($table_name=="programmes"||$table_name=="students")
{
$heading="Fitered ".str_replace("_"," ",$table_name)."' details...";
}
else
{
$heading="Fitered ".str_replace("_"," ",$table_name)."s' details...";
}
}
$qry=str_replace("@","'",$qury);
$colnamearray="";
$tmprst=mysql_query($qry);
$table_cols=mysql_query("SHOW COLUMNS FROM ".$table_name);
$header='<center>';
if($type=='doc')
{
$header=$header.'<h3><b>'.$heading.'</b></h3><br><table width="100%" border="1" style="border-collapse:collapse;">';
}
else
{
$header=$header.'<table width="100%">';
}
while($rowcolu=mysql_fetch_row($table_cols))
{
$Col_name=$rowcolu[0];
if(empty($colnamearray))
{
if($Col_name=="School_Id")
{
$colnamearray='School_Name';
}
else if($Col_name=="Competition_Id")
{
$colnamearray='Competition_Name';
}
else if($Col_name=="Programme_Id")
{
$colnamearray='Programme_Name';
}
else if($Col_name=="Resource_Id"&&$table_name=="")
{
$colnamearray='Resource_Type';
}
else if($Col_name=="Training_Id")
{
$colnamearray='Training_Name';
}
else if($Col_name=="Lab_Id"&&$table_name=="lab")
{
}
else if($Col_name=="Lab_Id"&&$table_name!="lab")
{
$colnamearray='Lab_Type';
}
else
{
$colnamearray=$rowcolu[0];
}
}
else
{
if($Col_name=="School_Id")
{
$colnamearray=$colnamearray.",".'School_Name';
}
else if($Col_name=="Competition_Id")
{
$colnamearray=$colnamearray.",".'Competition_Name';
}
else if($Col_name=="Programme_Id")
{
$colnamearray=$colnamearray.",".'Programme_Name';
}
else if($Col_name=="Resource_Id")
{
$colnamearray=$colnamearray.",".'Resource_Type';
}
else if($Col_name=="Training_Id")
{
$colnamearray=$colnamearray.",".'Training_Name';
}
else if($Col_name=="Lab_Id"&&$table_name=="lab")
{
$colnamearray=$colnamearray.",".'Lab_Id';
}
else if($Col_name=="Lab_Id"&&$table_name!="lab")
{
$colnamearray=$colnamearray.",".'Lab_Type';
}
else
{
$colnamearray=$colnamearray.",".$rowcolu[0];
}
}
}
if($table_name=="teacher")
{
if(empty($colnamearray))
{
$colnamearray="Resource_Person";
}
else
{
$colnamearray=$colnamearray.",Resource_Person";
}
if(empty($colnamearray))
{
$colnamearray="Training_Details";
}
else
{
$colnamearray=$colnamearray.",Training_Details";
}
}
if($table_name=="lab")
{
$qucolnet="SHOW COLUMNS FROM network";
$resultcolnet=mysql_query($qucolnet);
$totcolnet=mysql_num_rows($resultcolnet);
while($rowcolunet=mysql_fetch_row($resultcolnet))
{
if($rowcolunet[0]!="School_Id"&&$rowcolunet[0]!="Lab_Id")
{
if(empty($colnamearray))
{
$colnamearray=$rowcolunet[0];
}
else
{
$colnamearray=$colnamearray.",".$rowcolunet[0];
}
}
}
}
$cnamearray=explode(",",$colnamearray);
$body.="<tr>";
for($c=0;$c<count($cnamearray);$c++)
{
$cname1=str_replace("No_Of","Number_Of",$cnamearray[$c]);
$cname2=str_replace("T_NIC_Number","Teacher's_NIC_Number",$cname1);
$cname=str_replace("Teacher_NIC","Teacher's_NIC_Number",$cname2);
if($cnamearray[$c]!="Last_Updated_Date"&&$cnamearray[$c]!="Lab_Id")
{
$body.="<td>".str_replace("_"," ",$cname)."</td>";
}
}
$body.="</tr>";
$num_field=count($cnamearray);
$num_cols=mysql_num_rows($table_cols);
while($rowres=mysql_fetch_array($tmprst))
{
$body.="<tr>";
for($i=0;$i<$num_cols;$i++)
{
if($cnamearray[$i]=="Lab_Id"&&$table_name=="lab")
{
$L_ID=$rowres[$i];
}
else if($cnamearray[$i]!="Last_Updated_Date")
{
$body.='<td>';
if($cnamearray[$i]=="School_Name")
{
$S_ID=$rowres[$i];
$resultsch=mysql_query("SELECT * FROM school WHERE School_Id='".$rowres[$i]."'");
  if($rowsch=mysql_fetch_array($resultsch))
  {
$body.=$rowsch['Name'];
  }
  else
  {
  $body.="";
  }
}
else if($cnamearray[$i]=="Competition_Name")
{
$result=mysql_query("SELECT * FROM competitions WHERE Competition_Id='".$rowres[$i]."'");
  if($row=mysql_fetch_array($result))
  {
$body.=$row['Competition_Name'];
  }
  else
  {
  $body.="";
  }
}
else if($cnamearray[$i]=="Programme_Name")
{
$result=mysql_query("SELECT * FROM programme WHERE Programme_Id='".$rowres[$i]."'");
  if($row=mysql_fetch_array($result))
  {
$body.=$row['Programme_Name'];
  }
  else
  {
  $body.="";
  }
}
else if($cnamearray[$i]=="Resource_Type")
{
$result=mysql_query("SELECT * FROM resource WHERE Resource_Id='".$rowres[$i]."'");
  if($row=mysql_fetch_array($result))
  {
$body.=$row['Resource_Type'];
  }
  else
  {
  $body.="";
  }
}
else if($cnamearray[$i]=="Training_Name")
{
$result=mysql_query("SELECT * FROM training WHERE Training_Id='".$rowres[$i]."'");
  if($row=mysql_fetch_array($result))
  {
$body.=$row['Training_Name'];
  }
  else
  {
  $body.="";
  }
}
else if($cnamearray[$i]=="Lab_Type"&&$table_name!="lab")
{
$result=mysql_query("SELECT * FROM lab WHERE Lab_Id='".$rowres[$i]."'");
  if($row=mysql_fetch_array($result))
  {
$body.=$row['Lab_Type'];
  }
  else
  {
  $body.="";
  }
}
else if($cnamearray[$i]=="T_NIC_Number")
{
$T_NIC_Number=$rowres[$i];
$body.=str_replace("deg_"," ",$rowres[$i]);
}
else
{
$body.=str_replace("deg_"," ",$rowres[$i]);
}
$body.="</td>";
}
}
if($table_name=="lab")
{
$qucolnet="SHOW COLUMNS FROM network";
$resultcolnet=mysql_query($qucolnet);
$totcolnet=mysql_num_rows($resultcolnet);
$resultnet=mysql_query("SELECT * FROM network WHERE Lab_Id='".$L_ID."' AND School_Id='".$S_ID."'");
  if($rownet=mysql_fetch_array($resultnet))
  {
  while($rowcolunet=mysql_fetch_row($resultcolnet))
{
$column_Name_net=$rowcolunet[0];
if($column_Name_net!="Lab_Id"&&$column_Name_net!="School_Id")
{
if($column_Name_net!="Last_Updated_Date")
{
$body.='<td>';
$body.=$rownet[$column_Name_net];
$body.='</td>';
}
	 }
}
  }
 else
  {
  while($rowcolunet=mysql_fetch_row($resultcolnet))
{
$column_Name_net=$rowcolunet[0];
if($column_Name_net!="Lab_Id"&&$column_Name_net!="School_Id")
{
if($column_Name_net!="Last_Updated_Date")
{
$body.='<td>';
$body.='';
$body.='</td>';
	 }
	 }
	 }
  }
  }
  if($table_name=="teacher")
  {
  $resou=mysql_query("SELECT * FROM resource_team WHERE T_NIC_Number='".$T_NIC_Number."'");
  if($resou&&mysql_num_rows($resou)>0)
  {
  $body.="<td>Yes</td>";
  }
  else
  {
  $body.="<td>No</td>";
  }
  $details="";
  $tra_det=mysql_query("SELECT * FROM training_details WHERE Teacher_NIC='".$T_NIC_Number."'");
  if($tra_det)
  {
  while($row_tra_det=mysql_fetch_array($tra_det))
  {
  $Training_Id=$row_tra_det['Training_Id'];
  $tra=mysql_query("SELECT * FROM training WHERE Training_Id='".$Training_Id."'");
  if($row_tra=mysql_fetch_array($tra))
  {
  if(empty($details))
  {
  $details=$row_tra['Training_Name'].":".$row_tra_det['Duration'];
  }
  else
  {
  $details=$details.",".$row_tra['Training_Name'].":".$row_tra_det['Duration'];
  }
  }
  }
  }
  if(!empty($details))
  {
  $body.="<td>".$details."</td>";
  }
  else
  {
  $body.="<td>_</td>";
  }
  }
$body.="</tr>";
}
if($type=='xls')
{
$this->setHeaderXLS($file_name);
}
else if($type=='doc')
{
$this->setHeaderDoc($file_name);
}
else if($type=='csv')
{
$this->setHeaderCSV($file_name);
}
echo $header.$body."</table>";
}
}
?>